import pandas as pd
import numpy as np
from sklearn.metrics import accuracy_score, mean_squared_error, r2_score, mean_squared_log_error, mean_absolute_error
from sklearn.preprocessing import StandardScaler,MinMaxScaler
from scipy.spatial import distance
from sklearn.tree import DecisionTreeRegressor
from sklearn.linear_model import LinearRegression, SGDRegressor
from sklearn.ensemble import RandomForestRegressor, GradientBoostingRegressor, AdaBoostRegressor, BaggingRegressor, ExtraTreesRegressor
import sklearn.model_selection as ms
from sklearn.neighbors import KNeighborsRegressor
import xgboost as xgb
from sklearn.linear_model import Lasso, Ridge, ElasticNet
import datetime
from sqlalchemy import create_engine
from sqlalchemy.pool import NullPool
from SKO.AbstractDPJob import AbstractDPJob
from Predict_FESJob import Predict_FESJob
class Predict_6626_2Job(AbstractDPJob):
    def __init__(self,
                 p_st_no=None, p_aim_st_s=None,p_recv_s=None):
        super(Predict_6626_2Job, self).__init__()
        self.st_no = p_st_no
        self.aim_st_s = p_aim_st_s
        self.recv_s = p_recv_s
        pass
    def execute(self):
        return self.do_execute()
    def do_execute(self):
        super(Predict_6626_2Job, self).do_execute()
        # 预测二炼钢硫平衡接口传入参数
        # 出钢记号、计划钢种目标S
        st_no = self.st_no
        aim_st_s = self.aim_st_s
        recv_s = self.recv_s
        msg = ''



        DB_HOST_MPP_DB2 = '10.70.48.41'
        DB_PORT_MPP_DB2 = 50021
        DB_DBNAME_MPP_DB2 = 'BGBDPROD'
        DB_USER_MPP_DB2 = 'm1admin'
        DB_PASSWORD_MPP_DB2 = 'm1adminbdzg'

        def getConnectionDb2(host, port, dbname, user, password):
            # conn = pg.connect(host=host, port=port, dbname=dbname, user=user, password=password)
            engine = create_engine('ibm_db_sa://' + user + ':' + password + '@' + host + ':' + str(port) + '/' + dbname,
                                   encoding="utf-8", poolclass=NullPool)
            return engine.connect()

        # db_conn_mpp = getConnectionDb2(DB_HOST_MPP_DB2,
        #                                DB_PORT_MPP_DB2,
        #                                DB_DBNAME_MPP_DB2,
        #                                DB_USER_MPP_DB2,
        #                                DB_PASSWORD_MPP_DB2)

        # 根据出钢记号、计划钢种目标S，进行分组
        if st_no == 'IH2554A2':
            group = 1
            sql_condition = " AND ST_NO = 'IH2554A2'"
        elif st_no[:2] == 'IH' and st_no != 'IH2554A2':
            group = 2
            sql_condition = " AND ST_NO != 'IH2554A2' AND left(ST_NO,2) = 'IH' "
        elif st_no[:2] == 'IW':
            group = 3
            sql_condition = " AND left(ST_NO,2) = 'IW' "
        elif aim_st_s <= 15 and st_no[:2] not in ['IH', 'IW']:
            group = 4
            sql_condition = " AND left(ST_NO,2) not in ('IW','IH') AND AIM_ST_S <= 15"
        elif aim_st_s <= 20 and aim_st_s > 15 and st_no[:2] not in ['IH', 'IW']:
            group = 5
            sql_condition = " AND left(ST_NO,2) not in ('IW','IH') AND AIM_ST_S > 15 AND AIM_ST_S <= 20"
        elif aim_st_s <= 30 and aim_st_s > 20 and st_no[:2] not in ['IH', 'IW']:
            group = 6
            sql_condition = " AND left(ST_NO,2) not in ('IW','IH') AND AIM_ST_S > 20 AND AIM_ST_S <= 30"
        elif aim_st_s <= 50 and aim_st_s > 30 and st_no[:2] not in ['IH', 'IW']:
            group = 7
            sql_condition = " AND left(ST_NO,2) not in ('IW','IH') AND AIM_ST_S > 30 AND AIM_ST_S <= 50"
        elif aim_st_s <= 100 and aim_st_s > 50 and st_no[:2] not in ['IH', 'IW']:
            group = 8
            sql_condition = " AND left(ST_NO,2) not in ('IW','IH') AND AIM_ST_S > 50 AND AIM_ST_S <= 100"
        elif aim_st_s < 150 and aim_st_s > 100 and st_no[:2] not in ['IH', 'IW']:
            group = 9
            sql_condition = " AND left(ST_NO,2) not in ('IW','IH') AND AIM_ST_S > 100 AND AIM_ST_S < 150"
        elif aim_st_s < 180 and aim_st_s >= 150 and st_no[:2] not in ['IH', 'IW']:
            group = 10
            sql_condition = " AND left(ST_NO,2) not in ('IW','IH') AND AIM_ST_S >= 150 AND AIM_ST_S < 180"
        elif aim_st_s <= 250 and aim_st_s >= 180 and st_no[:2] not in ['IH', 'IW']:
            group = 11
            sql_condition = " AND left(ST_NO,2) not in ('IW','IH') AND AIM_ST_S >= 180 AND AIM_ST_S <= 250"
        else:
            group = 12
            sql_condition = " AND left(ST_NO,2) not in ('IW','IH') AND AIM_ST_S > 250"
        #本地编写目前读取本地文件，后续使用sql_condition进行拼接SQL
        start = datetime.datetime.now()
        delta_day2 = 10
        delta_day1 = delta_day2 + 365
        p_day_2 = (start - datetime.timedelta(days=delta_day2)).strftime("%Y%m%d")
        p_day_1 = (start - datetime.timedelta(days=delta_day1)).strftime("%Y%m%d")
        sql = " select PROD_DATE,ST_NO,AIM_ST_S,DES_WHISK_DEPTH1,STTEMP_AFIRON,AFTEMP_AFIRON  " \
              " FROM BGTAMOMMSM.T_ODS_TMMSM0701  " \
              " WHERE TC_PROC_FlAG = 2 AND PROD_DATE>='%s' " \
              " AND PROD_DATE<'%s' " \
              " %s ORDER BY PROD_DATE " % (p_day_1, p_day_2, sql_condition)
        print(sql)
        # df = pd.read_sql_query(sql, con=db_conn_mpp)
        # if df.empty is True:
        #     msg = '该钢种无分组'

        xlsx_name = 'D:/repos/sicost/test_0607.xlsx'
        df = pd.read_excel(xlsx_name)
        df.columns = df.columns.str.upper()
        df['PROD_DATE'] = df['PROD_DATE'].astype(str)

        def clean_data(df, gamma):

            column_name_list = df.columns.tolist()
            column_name_list.remove('PROD_DATE')
            column_name_list.remove('ST_NO')
            column_name_list.remove('AIM_ST_S')
            column_name_num = len(column_name_list)
            clean_str_start = 'df_new = df['
            clean_str_end = ']'
            ldict1 = {}
            for i in range(0, column_name_num):
                # print(i)
                # print(column_name_list[i])
                column_name_tmp = column_name_list[i]
                exec('''clean_str3 = "(df['{}'] > 0)"'''.format(column_name_tmp), locals(), ldict1)
                clean_str3 = ldict1["clean_str3"]
                if i == 0:
                    clean_str_start = clean_str_start + clean_str3
                else:
                    clean_str_start = clean_str_start + ' & ' + clean_str3
            clean_str = clean_str_start + clean_str_end
            # print(clean_str)
            exec(clean_str, locals(), ldict1)
            df_new = ldict1["df_new"]
            df_new = df_new.reset_index(drop=True)
            return df_new
        gamma = 1.5
        df_clean1 = clean_data(df, gamma)
        df_clean1['TEMP'] = df_clean1['STTEMP_AFIRON'] - df_clean1['AFTEMP_AFIRON']
        df_clean2 = df_clean1[(df_clean1['TEMP'] > 0)]
        df_clean2 = df_clean2.reset_index(drop=True)
        df_clean2.drop(['STTEMP_AFIRON'], axis=1, inplace=True)
        df_clean2.drop(['AFTEMP_AFIRON'], axis=1, inplace=True)
        # model = LinearRegression()
        # X = df_clean2['DES_WHISK_DEPTH1'].values.reshape(-1, 1)
        # y = df_clean2['TEMP'].values
        # model.fit(X, y)
        # wenjiang_coef = float(model.coef_[0])
        recv_s_max_list2 = [0, 200, 300, 400, 500]

        df_out2 = pd.DataFrame(
            columns=['RECV_S_MAX', 't1', 't2'])
        dict = {}
        for i in range(0, len(recv_s_max_list2)):
            print(recv_s_max_list2[i])
            if i == 0:
                interval = recv_s_max_list2[i + 1] - recv_s_max_list2[i]
            else:
                interval = recv_s_max_list2[i] - recv_s_max_list2[i - 1]
            min_tmp = recv_s_max_list2[i] - interval / 10
            max_tmp = recv_s_max_list2[i] + interval / 10
            df7_copy = df_clean2.copy()
            df7_copy['DELTA_RECV_S'] = abs(df7_copy['RECV_S'] - recv_s_max_list2[i])
            df7_copy_sorted = df7_copy.sort_values(by='DELTA_RECV_S')
            # , ascending = False
            df7_copy_sorted = df7_copy_sorted.reset_index(drop=True)
            df7_tmp2 = df7_copy_sorted.head(10)
            df7_tmp1 = df7_copy[(df7_copy['RECV_S'] <= max_tmp) & (df7_copy['RECV_S'] >= min_tmp)]
            df7_tmp3 = df7_copy[(df7_copy['RECV_S'] == recv_s_max_list2[i])]
            row_count1 = len(df7_tmp1)
            row_count3 = len(df7_tmp3)
            if row_count3 > 0:
                y1_pred = df7_tmp3['TEMP'].mean()
                print(y1_pred)
            else:
                y1_pred = 0
            dict['RECV_S_MAX'] = recv_s_max_list2[i]
            dict['t1'] = y1_pred
            if row_count1 <= 5:
                df7_tmp = df7_tmp2
            else:
                df7_tmp = df7_tmp1
            model = LinearRegression()
            X = df7_tmp['RECV_S'].values.reshape(-1, 1)
            y = df7_tmp['TEMP'].values
            model.fit(X, y)
            list_tmp = []
            list_tmp.append(recv_s_max_list2[i])
            array_tmp = np.array(list_tmp).reshape(-1, 1)
            y_pred = model.predict(array_tmp)
            # print(y_pred)
            y1_pred = y_pred[0]
            print(y1_pred)
            dict['t2'] = y1_pred
            new_row = pd.Series(dict)
            df_out2 = df_out2.append(new_row, ignore_index=True)
        df_out2_copy = df_out2.copy()
        df_out2_copy = df_out2_copy[df_out2_copy['t1'] != 0]
        df_out2_copy = df_out2_copy.reset_index(drop=True)
        model = LinearRegression()
        X = df_out2_copy['RECV_S_MAX'].values.reshape(-1, 1)
        y = df_out2_copy['t1'].values
        model.fit(X, y)
        for index, row in df_out2.iterrows():
            if row['t1'] == 0:
                x_pred = row['RECV_S_MAX']
                list_tmp = []
                list_tmp.append(x_pred)
                array_tmp = np.array(list_tmp).reshape(-1, 1)
                y_pred = model.predict(array_tmp)
                # print(y_pred)
                y1_pred = y_pred[0]
                row['t1'] = y1_pred
        print(df_out2)
        values = df_out2.loc[df_out2['RECV_S_MAX'] == recv_s_max_list2[0], 't1']
        t1_min = float(values)
        values = df_out2.loc[df_out2['RECV_S_MAX'] == recv_s_max_list2[0], 't2']
        t2_min = float(values)
        values = df_out2.loc[df_out2['RECV_S_MAX'] == recv_s_max_list2[-1], 't1']
        t1_max = float(values)
        values = df_out2.loc[df_out2['RECV_S_MAX'] == recv_s_max_list2[-1], 't2']
        t2_max = float(values)
        t_min = min(t1_min, t2_min)
        t_max = max(t1_max, t2_max)
        df_out2['t3'] = df_out2['t2']
        df_out2['t4'] = df_out2['t2']
        for index, row in df_out2.iterrows():
            if index == 0:
                row['t3'] = t_min
                row['t4'] = t_min
            elif index == len(recv_s_max_list2) - 1:
                row['t3'] = t_max
                row['t4'] = t_max
            else:
                t3_tmp = t_min + (t_max - t_min) / (recv_s_max_list2[-1] - recv_s_max_list2[0]) * (
                            row['RECV_S_MAX'] - recv_s_max_list2[0])
                row['t3'] = t3_tmp
        df_out2['t4_next'] = df_out2['t4'].shift(-1)
        df_out2.t4_next.fillna(99999, inplace=True)
        df_out2['t4_pred'] = df_out2['t4'].shift(1)
        df_out2.t4_pred.fillna(0, inplace=True)
        change_value = 0
        df_out2['t_tmp'] = df_out2['t4']
        for index, row in df_out2.iterrows():
            if index != 0 and index != len(recv_s_max_list2) - 1:
                # 检验
                print(row['t4_pred'])
                print(change_value)
                if row['t4'] > change_value and row['t4'] < row['t4_next']:
                    change_value = row['t4']
                else:
                    print('需要改变')
                    new_value1 = average(row['t3'], row['t4'])
                    if new_value1 > change_value and new_value1 < row['t4_next']:
                        change_value = new_value1
                    else:
                        new_value2 = average(row['t3'], new_value1)
                        if new_value2 > change_value and new_value2 < row['t4_next']:
                            change_value = new_value2
                        else:
                            change_value = row['t3']
            else:
                change_value = row['t4']
            row['t_tmp'] = change_value
        df_out2 = df_out2[['RECV_S_MAX', 't_tmp']]
        df_out2.rename(columns={'t_tmp': 'TEMP'}, inplace=True)
        print(df_out2)
        def cal_temp(p_recv_s):
            recv_s = p_recv_s

            for index, row in df3.iterrows():
                recv_s_max_tmp = row['RECV_S_MAX']
                temp_max_tmp = row['TEMP']
                recv_s_max_next_tmp = row['RECV_S_MAX_NEXT']
                temp_max_next_tmp = row['TEMP_NEXT']
                print(index)
                if recv_s <= 500:
                    temp_unit = (temp_max_next_tmp - temp_max_tmp) / (
                            recv_s_max_next_tmp - recv_s_max_tmp) * 10
                else:
                    temp_unit = temp_unit_1
                if index == 0:
                    if recv_s <= recv_s_max_tmp:
                        temp_tmp = temp_max_tmp - (recv_s_max_tmp - recv_s) * temp_unit / 10
                        break
                    elif recv_s > recv_s_max_tmp and recv_s <= recv_s_max_next_tmp:
                        temp_tmp = temp_max_tmp + (recv_s - recv_s_max_tmp) * temp_unit
                        break
                    else:
                        continue
                else:
                    if recv_s > recv_s_max_tmp and recv_s <= recv_s_max_next_tmp:
                        tempe_tmp = temp_max_tmp + (recv_s - recv_s_max_tmp) * temp_unit / 10
                        break
                    else:
                        continue
            return temp_tmp

        wenjiang = cal_temp(recv_s * 10)


        return wenjiang